package org.jiaxin.nutrient.utils;

import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.parser.Feature;
import org.apache.commons.lang3.StringUtils;
import org.jiaxin.nutrient.config.SpringBootConfig;
import org.springframework.beans.factory.annotation.Value;

import java.io.IOException;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.*;

/**
 * @Author: jiaxin
 * @Description : TODO 数据库操作模板类,可以将数据库的表跟JavaBean对象进行1:1映射
 * @date: 2023/8/25 12:13
 **/

public class DBTemplate<T> {
    private final Connection connection;

    public static <T> DBTemplate<T> getInstance() throws IOException {
        //获取数据库连接
        SpringBootConfig springBootConfig = new SpringBootConfig();
        Connection connection = JdbcConnection.getMysqlConnection(springBootConfig.getDbUsername(), springBootConfig.getDbPassword(), springBootConfig.getDbUrl());
        return new DBTemplate<T>(connection);
    }

    public DBTemplate(Connection connection){
        this.connection=connection;
    }

    public Connection getConnection() {
        return connection;
    }

    /**
     * TODO 根据传入的sql获取到符合这个sql的所有数据
     * @param sql
     * @param clazz
     * @return
     * @throws Exception
     */
    public List<T> getSqlData (String sql, Class<T> clazz) throws Exception {
        //所有的结果都放到集合中
        List<T> instanceList = new LinkedList<>();

        Map<String, Field> classMap = getClassFieldMap(clazz);
        //执行sql

        ResultSet resultSet = connection.prepareStatement(sql).executeQuery();
        Set<String> keySet = classMap.keySet();

        while (resultSet.next()) {
            //new 一个T对象
            T instance = clazz.newInstance();
            for (String fieldName : keySet) {
                Field field = classMap.get(fieldName);
                field.setAccessible(true);
                switch (field.getType().getName()) {
                    case "java.lang.String":
                        String strData = resultSet.getString(CommonUtils.camelToUnderLine(fieldName));
                        field.set(instance, strData);
                        break;
                    case "java.lang.Integer":
                        Integer integerData = resultSet.getInt(CommonUtils.camelToUnderLine(fieldName));
                        field.set(instance, integerData);
                        break;
                    case "java.lang.Double":
                        Double doubleData = resultSet.getDouble(CommonUtils.camelToUnderLine(fieldName));
                        field.set(instance, doubleData);
                        break;
                    case "java.lang.Long":
                        Long longData = resultSet.getLong(CommonUtils.camelToUnderLine(fieldName));
                        field.set(instance, longData);
                        break;
                    case "java.lang.Float":
                        Float floatData = resultSet.getFloat(CommonUtils.camelToUnderLine(fieldName));
                        field.set(instance, floatData);
                        break;
                    case "java.lang.Boolean":
                        boolean booleanData = resultSet.getBoolean(CommonUtils.camelToUnderLine(fieldName));
                        field.setBoolean(instance, booleanData);
                        break;
                    case "com.alibaba.fastjson.JSONObject":
                        JSONObject jsonObject = JSONObject.parseObject(resultSet.getString(CommonUtils.camelToUnderLine(fieldName)), Feature.OrderedField);
                        field.set(instance, jsonObject);
                        break;
                    default:
                        System.out.println("未知类型: " + field.getName());
                        break;
                }
            }
            instanceList.add(instance);
        }
        return instanceList;
    }

    /**
     * TODO 根据传入的表名，获取该表的所有数据
     * @param tableName
     * @param clazz
     * @return
     * @throws Exception
     */
    public List<T> getTableAllData(String tableName, Class<T> clazz) throws Exception {
        String sql="select * from "+tableName;
        return getSqlData(sql, clazz);
    }


    /**
     * TODO 根据sql某些条件返回符合条件的1条数据，如果返回多条数据或者0条数据 则抛出异常.
     * @param sql
     * @param clazz
     * @return
     * @throws Exception
     */
    public T getSqlOneData(String sql, Class<T> clazz) throws Exception {
        List<T> allData = getSqlData(sql, clazz);
        if (allData.size()!=1){
            throw new Exception("该表得到的数据量为: "+allData.size()+" != 1 请重新输入！！");
        }
        return allData.get(0);
    }


    public Long getTableRowNum(String sql) throws SQLException {
        ResultSet resultSet = connection.prepareStatement(sql).executeQuery();
        long num = 0;
        while (resultSet.next()){
            num=resultSet.getLong(1);
        }
        return num;
    }
    /***
     *
     */
    public void deleteDataByOneCol(String tableName,String colName,Object value) throws Exception {
        HashMap<String, Object> hashMap = new HashMap<>();
        hashMap.put(colName,value);
        deleteDataByMultipleCol(tableName,hashMap);
    }


    public void deleteDataByMultipleCol(String tableName,Map<String,Object> colValueMap) throws Exception {
        Map<String, String> dataWithMap = JDBCUtils.getSqlServerMateDataWithMap(connection, tableName);

        Set<String> keySet = colValueMap.keySet();
        StringBuilder sqlBuilder= new StringBuilder("delete from " + tableName + " where "); //delete from xxx where c1=? and c2=? and c3=?
        for (String colName : keySet) {
            sqlBuilder.append(colName).append("=?").append(" and ");
        }
        //删除最后一个 and
        sqlBuilder.delete(sqlBuilder.length()-4,sqlBuilder.length());
        //System.out.println(sqlBuilder);

        PreparedStatement preparedStatement = connection.prepareStatement(sqlBuilder.toString());
        int i=1;
        for (String colName : keySet) {
            String colType = dataWithMap.get(colName);
            Object value = colValueMap.get(colName);
            buildValue(preparedStatement,colType,value,i);
            i++;
        }
        preparedStatement.executeUpdate();
    }





    //根据JavaBean 类进行 upsert into 操作 如果主键重复就更新，没有的话就插入
    public void saveOrUpdate(String tableName,Class<T> clazz,T t,boolean isUnderlineToCamel) throws Exception {
        //获取 数据表格对应的schema
        Map<String, String> dataWithMap = JDBCUtils.getSqlServerMateDataWithMap(connection, tableName);
        Set<String> keySet = dataWithMap.keySet();
        String insertOrUpdateSql = buildInsertOrUpdateSql(tableName, keySet, true);

        System.out.println(insertOrUpdateSql);
        PreparedStatement preparedStatement = connection.prepareStatement(insertOrUpdateSql);
        // 给插入更新的 占位符 赋值
        int i=1;
        for (int round = 0;  round<2 ; round++) {
            for (String colName : keySet) {
                if (round!=0){
                    if ("create_time".equalsIgnoreCase(colName)){
                        continue;
                    }
                }
                i = getFiled(clazz, t, isUnderlineToCamel, dataWithMap, preparedStatement, i, colName);
            }
        }
        preparedStatement.executeUpdate();
    }


    //根据JavaBean类 对数据进行插入操作 如果主键重复，则提示报错

    public void  save (String tableName,Class<T> clazz,T t,boolean isUnderlineToCamel)  throws Exception {
        Map<String, String> dataWithMap = JDBCUtils.getSqlServerMateDataWithMap(connection, tableName);
        Set<String> keySet = dataWithMap.keySet();
        String insertSql = buildInsertOrUpdateSql(tableName,keySet,false);
        //System.out.println(insertSql);
        PreparedStatement preparedStatement = connection.prepareStatement(insertSql);
        //开始设置占位符
        int i=1;
        for (String colName : keySet) {
            i = getFiled(clazz, t, isUnderlineToCamel, dataWithMap, preparedStatement, i, colName);
        }
        //执行SQL
        preparedStatement.executeUpdate();
    }


    /*public void update (String tableName,Class<T> clazz,T t,boolean isUnderlineToCamel) throws Exception {
        Map<String, String> dataWithMap = JDBCUtils.getSqlServerMateDataWithMap(connection, tableName);
        Set<String> keySet = dataWithMap.keySet();





    }*/






    private int getFiled(Class<T> clazz, T t, boolean isUnderlineToCamel, Map<String, String> dataWithMap, PreparedStatement preparedStatement, int i, String colName) throws NoSuchFieldException, IllegalAccessException, SQLException {
        Field field = clazz.getDeclaredField(isUnderlineToCamel? CommonUtils.underlineToCamel(colName):colName);
        field.setAccessible(true);
        Object value = field.get(t);
        String colType = dataWithMap.get(colName);
        buildValue(preparedStatement,colType,value,i);
        i++;
        return i;
    }


    private String buildInsertOrUpdateSql(String tableName,Set<String> keySet,boolean haveUpdate) throws Exception {

        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("insert into ").append(tableName);
        stringBuilder.append(keySet.toString().replace("[", "(").replace("]",")")).append(" values ( ");
        int size = keySet.size();

        for (int i = 0; i <size ; i++) {
            stringBuilder.append("?").append(",");
        }
        stringBuilder.deleteCharAt(stringBuilder.length()-1).append(")");
        if (haveUpdate){
            //包含update的话
            stringBuilder.append(" ON DUPLICATE KEY UPDATE ");
            for (String colName : keySet) {
                if (colName.equalsIgnoreCase("create_time")){
                    continue;
                }
                stringBuilder.append(colName).append("= ?").append(",");
            }
            //去掉最后一个,号
            stringBuilder.deleteCharAt(stringBuilder.length()-1);

            return stringBuilder.toString();
        }

        return stringBuilder.toString();
    }


    private String buildUpdateSql(String tableName,Set<String> keySet){
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("update ").append(tableName);


        return null;

    }




    private void buildValue(PreparedStatement preparedStatement, String colType, Object value, int i) throws SQLException {
        if (colType.contains("varchar")){
            if (value==null) {
                preparedStatement.setNull(i,Types.VARCHAR);
            }else {
                preparedStatement.setObject(i, value);
            }
        }else if (colType.equalsIgnoreCase("int")){
            if (value==null) {
                preparedStatement.setNull(i,Types.INTEGER);
            }else {
                preparedStatement.setObject(i, value);
            }
        }else if (colType.equalsIgnoreCase("double")){
            if (value==null) {
                preparedStatement.setNull(i,Types.DOUBLE);
            }else {
                preparedStatement.setObject(i, value);
            }
        }else {
            if (value==null) {
                preparedStatement.setNull(i,Types.VARCHAR);
            }else {
                preparedStatement.setObject(i, value);
            }
        }

    }




    public void close() throws SQLException {
        if (connection!=null){
            connection.close();
        }

    }

    private Map<String,Field> getClassFieldMap(Class<T> clazz){
        //获取该类的所有的成员变量
        Field[] fields = clazz.getDeclaredFields();
        //类的 schema 信息
        Map<String, Field> classMap = new LinkedHashMap<String, Field>();
        for (Field field : fields) {
            field.setAccessible(true);
            String fieldName = field.getName();
            classMap.put(fieldName, field);
        }
        return classMap;
    }

    //执行命令 返回值void

    public int doExecuteSql(String sql) throws SQLException {
        if (StringUtils.isNoneBlank(sql)){
           return this.connection.prepareStatement(sql).executeUpdate();
        }
        throw new SQLException("收入的sql 不得为null 或者 空"+sql);
    }
}
